/**
 * Copyright 2011 Joe Choi, All rights reserved.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0

 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package taobe.tec.reslite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;

/**
 * @author Joe Choi
 */
public class Text {
	public Text(String pathToDB) {
		connectURL = "jdbc:sqlite:" + pathToDB;
	}

	public void create(String resource, String text) throws ClassNotFoundException, SQLException {
		create(resource, text, text, null);
	}
	
	public void create(String resource, String textId, String text) throws ClassNotFoundException, SQLException {
		create(resource, textId, text, null);
	}
	
	public void create(String resource, String textId, String text, Locale locate) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection(connectURL);
		Statement stm = conn.createStatement();
		createTextTable(stm);
		createTextLocaleTable(stm);
		stm.close();
		
		createText(conn, resource, textId, text, locate);
		conn.close();	
	}
		
	private void createTextTable(Statement stm) throws SQLException {
		String sql = "CREATE TABLE IF NOT EXISTS resm_text(" +
					 "res_name TEXT NOT NULL," +
					 "text_id TEXT NOT NULL," +
					 "PRIMARY KEY(res_name, text_id)" +
					 ")";
		stm.execute(sql);
	}
	
	private void createTextLocaleTable(Statement stm) throws SQLException {
		String sql = "CREATE TABLE IF NOT EXISTS resm_text_locale(" +
					 "text_id TEXT NOT NULL," +
					 "text TEXT NOT NULL," +
					 "loc_lang TEXT," +
					 "loc_country TEXT," +
					 "loc_variant TEXT," +
					 "PRIMARY KEY(text_id, loc_lang, loc_country, loc_variant)" +
					 ")";
		stm.execute(sql);
	}
	
	private void createText(Connection conn, String resource, String textId, String text, Locale loc) throws SQLException {
		String insText = "INSERT INTO resm_text(res_name, text_id) VALUES(?, ?)";
		String insTextLoc = "INSERT INTO resm_text_locale(" +
							"text_id, text, loc_lang, loc_country, loc_variant" +
							") VALUES (" +
							"?, ?, ?, ?, ?" +
							")";
		
		PreparedStatement stm1 = null; 
		PreparedStatement stm2 = null;

		try {
			conn.setAutoCommit(false);
			stm1 = conn.prepareStatement(insText);
			stm1.setString(1, resource);
			stm1.setString(2, textId);
			stm1.execute();
		
			stm2 = conn.prepareStatement(insTextLoc);
			stm2.setString(1, textId);
			stm2.setString(2, text);
			stm2.execute();
			if(loc != null) {
				stm2.setString(3, loc.getLanguage());
				stm2.setString(4, loc.getCountry());
				stm2.setString(5, loc.getVariant());
			} else {
				stm2.setString(3, null);
				stm2.setString(4, null);
				stm2.setString(5, null);				
			}
			conn.commit();
		} catch(SQLException e) {
			conn.rollback();
			throw(e);
		} finally {
			if(stm1 != null && (! stm1.isClosed())) stm1.close();
			if(stm2 != null && (! stm2.isClosed())) stm2.close();			
		}
	}

	private String connectURL;
}